import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host="localhost",
            user="root",
            passwd="123456",
            db="mlc",
            port=3307,
            charset="utf8"
        )

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql ="""
        SELECT order_id,age,user_count,id_no,user_name,phone FROM(o.id as order_id,u.id_no,u.user_name,u.phone,
        case
            when length(u.id_no) = 18 THEN YEAR(now()) - CAST(SUBSTR(u.id_no,7,4) as signed)
                else NULL
                end as age,
            (SELECT COUNT(*) FROM ticket_order_user_rel WHERE order_id = o.id) as user_count
        FROM ticket_order o JOIN ticket_order_user_rel  u on u.order_id = o.id WHERE u.id_no is not null and u.id_no ! = ''
        ) as subquery
        WHERE user_count = 1 and (age < 18 or age >= 60)
        """
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df.head)
        df.to_csv('./outlier/scenic_data.csv')

                
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()